package ac.manchester.cs.afzal.autoservd.Descriptors;

import java.sql.ResultSet;
import java.sql.SQLException;
import ac.manchester.cs.afzal.autoservd.sql_handler.SQL_Handler;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author hamafz
 */
public class SQL_Handler_Descriptors extends SQL_Handler
{

    /**
     *
     */
    private int ngram_id = 0;
    private Vector sen_ids;
    private Vector descriptors, descriptors_filtered, descriptors_freq_1, descriptors_freq_2, descriptors_category;

    /**
     *
     */
    public SQL_Handler_Descriptors()
    {
    }

    /**
     * Retrieve values from ___temp table to load the service descriptors into
     * __bioinformatics_service_descriptors table. Temp table acts as a buffer
     * here to find the frequency of these descriptors in the service descriptions
     */
    public void get_fields_from_temp_table()
    {
        descriptors = new Vector();
        descriptors_filtered = new Vector();
        descriptors_freq_1 = new Vector();
        descriptors_category = new Vector();
        try
        {
            statement = connection.createStatement();
            String query = "SELECT Value, Count(Value) As Freq_1, Filtered_Value," +
                    " Category FROM ___temp where filtered_value != 'EMPTY' group by Value";
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                descriptors.add(rs.getString("Value"));
                descriptors_filtered.add(rs.getString("Filtered_Value"));
                descriptors_freq_1.add(rs.getInt("Freq_1"));
                descriptors_category.add(rs.getString("Category"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception retrieving descriptor freq from corpus : " + sqlex.getMessage());
        }
    }

    /**
     *  Returns the top 300 most related terms to the topic (Bioinformatics in this case)
     *  from the Results of Term Classification experiment (using the termclassifier)
     * @return
     */
    public Vector get_top_300_from_results_final()
    {
        descriptors = new Vector();
        try
        {
            statement = connection.createStatement();
            String query = "SELECT distinct t.Value FROM results_final r, terms t " +
                    "where r.Term_ID = t.Term_ID order by Relevance desc limit 300";
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                descriptors.add(rs.getString("Value"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception while retrieving top 300 terms : " + sqlex.getMessage());
        }
        return get_descriptors();
    }

    /**
     *
     * Find Frequency of descriptors in Corpus / Looking up Frequency
     * in Sentence_Descriptors table. String match is made using the
     * regular expression *descriptor*
     * @param descriptor
     * @return
     */
    public String get_descriptor_freq_in_corpus_sentence_descriptors(String descriptor)
    {
        int count = 0;
        try
        {
            statement = connection.createStatement();
            String query = "SELECT Count(Filtered_Descriptor)As Freq FROM __sentence_descriptors " +
                    "where Filtered_Descriptor LIKE '%" + descriptor + "%'";
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                count = rs.getInt("Freq");
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception retrieving descriptor freq from corpus : " + sqlex.getMessage());
        }

        return Integer.toString(count);
    }

    /**
     *
     */
    public void get_all_descriptor_fields()
    {
        descriptors = new Vector();
        descriptors_filtered = new Vector();
        descriptors_freq_1 = new Vector();
        descriptors_freq_2 = new Vector();
        descriptors_category = new Vector();
        try
        {
            statement = connection.createStatement();
            String query = "SELECT * FROM ___bioinformatics_service_descriptors";
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                descriptors.add(rs.getString("Descriptor"));
                descriptors_filtered.add(rs.getString("Filtered_Descriptor"));
                descriptors_freq_1.add(rs.getInt("Freq_1"));
                descriptors_freq_2.add(rs.getInt("Freq_2"));
                descriptors_category.add(rs.getString("Category"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception retrieving descriptor stats from Bioinformatics table: " + sqlex.getMessage());
        }
    }

    /**
     * Retrieves Descriptors from the "___bioinformatics_service_descriptors" table
     * @return
     */
    public Vector get_values_from_dictionary()
    {
        descriptors = new Vector();
        Vector descriptor_ids = new Vector();
        descriptors = new Vector();
        String query = "SELECT Descriptor_ID, Filtered_Descriptor FROM " +
                "___bioinformatics_service_descriptors";
        try
        {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                descriptor_ids.add(rs.getInt("Descriptor_ID"));
                descriptors.add(rs.getString("Filtered_Descriptor"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Descriptor from Dictionary : " + sqlex.getMessage());
        }
        return descriptor_ids;
    }

    /**
     * Returns the number of appearances of given ngram
     * @param ngram
     * @return
     */
    public int get_count_of_descriptors(String ngram)
    {
        int count = 0;
        String query;
        query = "SELECT NGram_ID,Freq FROM ___bioinformatics_service_descriptors_ngrams " +
                "where NGram = '" + ngram + "'";
        try
        {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                count = rs.getInt("Freq");
                ngram_id = rs.getInt("Ngram_ID");
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: ID,Freq from Dictionary Ngrams : " + sqlex.getMessage());
        }
        return count;
    }

    /**
     * Returns D_Sen_ID from __sentence_descriptors table
     * @return
     */
    public Vector get_distinct_sentences_from_descriptors()
    {
        Vector sentences = new Vector();
        String query;
        try
        {
            //query = "SELECT distinct D_Sen_ID FROM __sentence_descriptors where D_Sen_ID < 100000";
            query = "SELECT distinct D_Sen_ID FROM __sentence_descriptors where D_Sen_ID > 199999 and D_Sen_ID < 300000";
            //query = "SELECT distinct Sentence_ID FROM __sentence_descriptors where Sentence_ID < 50000";
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                sentences.add(rs.getString("D_Sen_ID"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Get Sentence IDs from Sentence Descriptors Table : " + sqlex.getMessage());
        }
        return sentences;
    }

    /**
     * Returns the Descriptor ID and Value from __Sentence_Descriptors table
     * @param ID
     * @return
     */
    public Vector get_descriptors_per_distinct_sentence(int d_sen_id)
    {
        Vector filtered_descriptor = new Vector();
        sen_ids = new Vector();
        try
        {
            String query = "SELECT Sen_Desc_ID,Filtered_Descriptor FROM __sentence_descriptors where D_Sen_ID = " + d_sen_id;
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                filtered_descriptor.add(rs.getString("Filtered_Descriptor"));
                sen_ids.add(rs.getInt("Sen_Desc_ID"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Finding IDs and Values for descriptor per Sentence : " + sqlex.getMessage());
        }
        return filtered_descriptor;
    }

    /**
     * Returns the number of appearances of given ngram in given Sentence
     * @param ngram
     * @param sentence_id
     * @return
     */
    public int get_count_of_descriptors(String ngram, int sentence_id)
    {
        int count = 0;
        String query;
        query = "SELECT NGram_ID,Freq FROM __sentence_descriptors_ngrams where Value = '" + ngram + "' and Sentence_ID = " + sentence_id;
        try
        {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                count = rs.getInt("Freq");
                ngram_id = rs.getInt("NGram_ID");
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: ID,Value from Sentence Descriptor : " + sqlex.getMessage());
        }

        if (count != 0)
        {
            return count;
        }
        else
        {
            query = "SELECT NGram_ID,Freq FROM __sentence_descriptors_ngrams_a where Value = '" + ngram + "' and Sentence_ID = " + sentence_id;
            try
            {
                statement = connection.createStatement();
                ResultSet rs = statement.executeQuery(query);
                while (rs.next())
                {
                    count = rs.getInt("Freq");
                    ngram_id = rs.getInt("NGram_ID");
                }
                statement.close();
            }
            catch (SQLException sqlex)
            {
                System.out.println("SQL Exception: ID,Value from Sentence Descriptor : " + sqlex.getMessage());
            }

            if (count != 0)
            {
                return count;
            }
            else
            {
                query = "SELECT NGram_ID,Freq FROM __sentence_descriptors_ngrams_a where Value = '" + ngram + "' and Sentence_ID = " + sentence_id;
                try
                {
                    statement = connection.createStatement();
                    ResultSet rs = statement.executeQuery(query);
                    while (rs.next())
                    {
                        count = rs.getInt("Freq");
                        ngram_id = rs.getInt("NGram_ID");
                    }
                    statement.close();
                }
                catch (SQLException sqlex)
                {
                    System.out.println("SQL Exception: ID,Value from Sentence Descriptor : " + sqlex.getMessage());
                }
                return count;
            }
        }
    }

    /**
     *
     * @param table_name
     * @param ngram_id
     * @param column_freq
     * @param freq_value
     */
    public void update_table_ngrams(String table_name, int ngram_id, String column_freq, String freq_value)
    {
        try
        {
            Statement stmt = connection.createStatement();
            String query = "UPDATE " + table_name + " SET " + column_freq + " = " + freq_value + " WHERE NGram_ID = " + ngram_id;
            stmt.executeUpdate(query);
        }
        catch (SQLException ex)
        {
            Logger.getLogger(SQL_Handler_Descriptors.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     *
     * @return
     */
    public Vector get_sen_ids()
    {
        return sen_ids;
    }

    /**
     * @return the descriptors
     */
    public Vector get_descriptors()
    {
        return descriptors;
    }

    /**
     * @return the descriptors_filtered
     */
    public Vector get_descriptors_filtered()
    {
        return descriptors_filtered;
    }

    /**
     * @return the descriptors_freq_1
     */
    public Vector get_descriptors_freq_1()
    {
        return descriptors_freq_1;
    }

    /**
     * @return the descriptors_freq_2
     */
    public Vector get_descriptors_freq_2()
    {
        return descriptors_freq_2;
    }

    /**
     * @return the descriptors_category
     */
    public Vector get_descriptors_category()
    {
        return descriptors_category;
    }

    /**
     * @return the ngram_id
     */
    public int get_ngram_id()
    {
        return ngram_id;
    }
}
